package com.wcity.manage.common.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

/**
 * Created by jn on 2016/12/16.
 * excel工具
 */
public class Excel<T> {

    /**
     * 导出到excel
     * 导出有get（getXxxXxx）方法的属性
     * 标题list与属性list顺序一一对应
     *
     * @param data 源数据
     * @param headers 列标题
     * @param propertyList 需要导出的属性值list
     * @param sheetName sheet名称
     * @param outputStream
     * @param fileType 文件类型 true xlsx    false xls
     */
    public void exportExcel(Collection<T> data, List<String> headers, List<String> propertyList,
                            String sheetName, OutputStream outputStream, boolean fileType){

        Workbook workbook = fileType ? new XSSFWorkbook() : new HSSFWorkbook();

        Sheet sheet = workbook.createSheet( sheetName );

        // 创建标题行
        Row row = sheet.createRow( 0 );
        for ( int i = 0; i < headers.size(); i++ ) {
            Cell cell = row.createCell( i );
            cell.setCellValue( headers.get( i ) );
        }

        int index = 0;

        for ( T t : data ) {
            index++;
            row = sheet.createRow( index );

            int loop = 0;

            for ( String property : propertyList ) {

                // 获取get方法
                String getMethodName = "get" + property.substring( 0, 1 ).toUpperCase() + property.substring( 1 );

                try {
                    Method getMethod = t.getClass().getMethod( getMethodName );
                    // 执行get方法
                    Object value = getMethod.invoke( t );

                    Cell cell = row.createCell( loop );
                    // 有get方法并且是导出属性
                    loop++;

                    // 类型转换 根据需要自行修改
                    if ( value instanceof Date) {
                        cell.setCellValue( DateUtil.getDate( (Date) value ) );
                    } else {
                        // 默认设置string类型
                        String cellValue = "";
                        if ( value != null ) cellValue = value.toString();
                        cell.setCellValue( cellValue );
                    }

                } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
                    Log.error( "===========导出excel异常===========" );
                    return;
                }
            }
        }

        try {
            // 输出
            workbook.write( outputStream );
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 导入excel
     * 标题列从第一列开始对应propertyList[0]所对应的属性
     * set（setXxxXxx）方法
     *
     * @param file 文件
     * @param propertyList 需要设置的属性
     * @param classpath 实体bean 包路径
     * @return
     * @throws IOException
     */
    public Object importExcel(MultipartFile file, List<String> propertyList, String classpath) throws Exception {

        if ( file == null || file.getSize() <= 0 ) return "文件上传失败";

        InputStream inputStream;
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            Log.error( "importExcel error, {}", e.getMessage(), e );
            return "文件解析失败";
        }

        String originName = file.getOriginalFilename();
        Workbook workbook;
        if ( originName.endsWith( "xlsx" ) || originName.endsWith( "xls" ) ) {
            try {
                workbook = originName.endsWith( "xlsx" ) ? new XSSFWorkbook( inputStream ) : new HSSFWorkbook( inputStream );
            } catch (IOException e) {
                Log.error( "importExcel error, {}", e.getMessage(), e );
                return "文件解析失败";
            }
        } else {
            return "文件格式错误";
        }

        Sheet sheet = workbook.getSheetAt( 0 );

        List<T> list = new ArrayList<>();

        // 获取标题行列数 为后面循环一行次数
        int headerLength = sheet.getRow( 0 ).getPhysicalNumberOfCells();

        int index = 0;
        for ( Row row : sheet ) {

            if ( index == 0 ) {
                index++;
                continue;
            }

            Object o = Class.forName( classpath ).newInstance();

            for ( int i = 0; i < headerLength; i++ ) {

                // 需要设置的属性
                String fieldName = propertyList.get( i );

                // 设置set方法
                String setMethodName = "set" + fieldName.substring( 0, 1 ).toUpperCase() + fieldName.substring( 1 );

                try {

                    Log.info( "=====" + setMethodName + "=====" );
                    // 获取set方法
                    Method setMethod = o.getClass().getMethod( setMethodName, String.class );

                    Cell cell = row.getCell( i );

                    Object value = cell.getStringCellValue();

                    // 执行set方法
                    String cellValue = "";
                    if ( value != null ) cellValue = cell.toString();
                    setMethod.invoke( o, cellValue );

                } catch (Exception e) {
                    Log.error( "===========导入excel异常===========" );
                    e.printStackTrace();
                    return null;
                }
            }
            list.add( (T) o );
        }

        return list;
    }

}
